SRHedgePolicyState

V8 Message Definiton

METADATA

Attribute	Value
Topic	5280-strategy-hedgepolicy
MLink Token	ClientTrading
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
hedgeTarget_at	enum - AssetType	PRI	'None'	HedgeTarget
hedgeTarget_ts	enum - TickerSrc	PRI	'None'	HedgeTarget
hedgeTarget_tk	VARCHAR(12)	PRI	''	HedgeTarget
accnt	VARCHAR(16)	PRI	''
clientFirm	VARCHAR(16)	PRI	''
hedgeSecKey_at	enum - AssetType		'None'
hedgeSecKey_ts	enum - TickerSrc		'None'
hedgeSecKey_tk	VARCHAR(12)		''
hedgeSecKey_yr	SMALLINT UNSIGNED		0
hedgeSecKey_mn	TINYINT UNSIGNED		0
hedgeSecKey_dy	TINYINT UNSIGNED		0
hedgeSecType	enum - SpdrKeyType		'None'
securityDesc	TINYTEXT		''
ssaleFlag	enum - ShortSaleFlag		'None'	current live short sale flag stock
positionType	enum - PositionType		'None'	current live position type future
hedgeState	enum - HedgePolicyState		'None'
hedgeText	TINYTEXT		''
uPrc	DOUBLE		0	hedge target underlier price
isDeltaMixed	enum - YesNo		'None'
accumulatorError	TINYTEXT		''
grpDeltaBot	DOUBLE		0	option delta bot in ctrlDe units
grpDeltaSld	DOUBLE		0
grpDDeltaBot	DOUBLE		0	option delta bot using uMid at time of option fill
grpDDeltaSld	DOUBLE		0
lastGrpFillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'
ctrlDeltaBot	DOUBLE		0	hedge target delta units bot
ctrlDeltaSld	DOUBLE		0
ctrlDDeltaBot	DOUBLE		0	hedge target delta bot using actual fill price
ctrlDDeltaSld	DOUBLE		0
lastCtrlFillDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'
absOptCn	INT		0	total number of option contracts executed in this hedge group
netDelta	DOUBLE		0	grpDeltaBot grpDeltaSld ctrlDeltaBot ctrlDeltaSld
netDDelta	DOUBLE		0	grpDDeltaBot grpDDeltaSld ctrlDDeltaBot ctrlDDeltaSld
openHedgeTime	FLOAT		0	size weighted average time in seconds hedge risk has been open executions to fills
hedgeTargetVol	FLOAT		0	implied expected today annualized volatility for hedge target
expectedPrcRange	FLOAT		0	SQRTavgHedgeTime x hedgeTargetVol cone
opnDeltaBot	DOUBLE		0	open risk delta bot in ctrlDe units
opnDeltaSld	DOUBLE		0
opnDDeltaBot	DOUBLE		0	open risk delta bot in ctrlDe units
opnDDeltaSld	DOUBLE		0
clsDeltaBot	DOUBLE		0	close risk delta bot in ctrlDe units
clsDeltaSld	DOUBLE		0
clsDDeltaBot	DOUBLE		0	close risk delta bot in ctrlDe units
clsDDeltaSld	DOUBLE		0
slippagePnL	FLOAT		0	opnDeltaBot clsDeltaPrcSld opnDeltaPrcBot opnDeltaSld opnDeltaPrcSld clsDeltaPrcBot
slippageUnitPnL	FLOAT		0	slippagePnL grpDeltaBot grpDeltaSld pnl unit share or fc
slippageNormPnL	FLOAT		0	slippageUnitPnL expectedPrcRange
minDDeltaBand	FLOAT		0
maxDDeltaBand	FLOAT		0
bParentNumber	BIGINT		0
bActiveSize	INT		0
bBrkrStatus	TINYTEXT		''
bBrkrError	TINYTEXT		''
sParentNumber	BIGINT		0
sActiveSize	INT		0
sBrkrStatus	TINYTEXT		''
sBrkrError	TINYTEXT		''
ddMult	DOUBLE		0
underliersPerCn	INT		0	future underliers per contract
underlierType	enum - UnderlierType		'None'	type of underlier affects greek calculations
pointValue	DOUBLE		0	NLV value of a single point change in display premium hedge target
pointCurrency	enum - Currency		'None'
liveHedgeMark	DOUBLE		0	hedgeSecKey live mark midmarket zero in SRSE
updtReason	VARCHAR(20)		''
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	SR system timestamp

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
hedgeTarget_tk	1
hedgeTarget_at	2
hedgeTarget_ts	3
accnt	4
clientFirm	5

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRHedgePolicyState` (
    `hedgeTarget_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'HedgeTarget',
    `hedgeTarget_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'HedgeTarget',
    `hedgeTarget_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'HedgeTarget',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '',
    `hedgeSecKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `hedgeSecKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `hedgeSecKey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `hedgeSecKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `hedgeSecKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `hedgeSecKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `hedgeSecType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None',
    `securityDesc` TINYTEXT NOT NULL DEFAULT '',
    `ssaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'current (live) short sale flag (stock)',
    `positionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None' COMMENT 'current (live) position type (future)',
    `hedgeState` ENUM('None','NoHedgeCtrl','MLegHold','InBand','BandSh','BandLn','Exception','DDMultErr','UndTypeErr','UndPerCnErr','Init','UPrcErr','AccumulatorError') NOT NULL DEFAULT 'None',
    `hedgeText` TINYTEXT NOT NULL DEFAULT '',
    `uPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedge target underlier price',
    `isDeltaMixed` ENUM('None','Yes','No') NOT NULL DEFAULT 'None',
    `accumulatorError` TINYTEXT NOT NULL DEFAULT '',
    `grpDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'option delta bot (in ctrlDe units)',
    `grpDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `grpDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'option $delta bot (using uMid at time of option fill)',
    `grpDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `lastGrpFillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `ctrlDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedge target delta (units) bot',
    `ctrlDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `ctrlDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedge target $delta bot (using actual fill price)',
    `ctrlDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `lastCtrlFillDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `absOptCn` INT NOT NULL DEFAULT 0 COMMENT 'total number of option contracts executed in this hedge group',
    `netDelta` DOUBLE NOT NULL DEFAULT 0 COMMENT 'grpDeltaBot - grpDeltaSld + ctrlDeltaBot - ctrlDeltaSld',
    `netDDelta` DOUBLE NOT NULL DEFAULT 0 COMMENT 'grpDDeltaBot - grpDDeltaSld + ctrlDDeltaBot - ctrlDDeltaSld',
    `openHedgeTime` FLOAT NOT NULL DEFAULT 0 COMMENT 'size weighted average time (in seconds) hedge risk has been open (executions to fills)',
    `hedgeTargetVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'implied / expected today (annualized) volatility for hedge target',
    `expectedPrcRange` FLOAT NOT NULL DEFAULT 0 COMMENT 'SQRT(avgHedgeTime x hedgeTargetVol) cone',
    `opnDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'open risk delta bot (in ctrlDe units)',
    `opnDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `opnDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'open risk $delta bot (in ctrlDe units)',
    `opnDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `clsDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'close risk delta bot (in ctrlDe units)',
    `clsDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `clsDDeltaBot` DOUBLE NOT NULL DEFAULT 0 COMMENT 'close risk $delta bot (in ctrlDe units)',
    `clsDDeltaSld` DOUBLE NOT NULL DEFAULT 0,
    `slippagePnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'opnDeltaBot * (clsDeltaPrcSld - opnDeltaPrcBot) + opnDeltaSld * (opnDeltaPrcSld - clsDeltaPrcBot)',
    `slippageUnitPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'slippagePnL / (grpDeltaBot + grpDeltaSld) [pnl / unit (share or fc)]',
    `slippageNormPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'slippageUnitPnL / expectedPrcRange',
    `minDDeltaBand` FLOAT NOT NULL DEFAULT 0,
    `maxDDeltaBand` FLOAT NOT NULL DEFAULT 0,
    `bParentNumber` BIGINT NOT NULL DEFAULT 0,
    `bActiveSize` INT NOT NULL DEFAULT 0,
    `bBrkrStatus` TINYTEXT NOT NULL DEFAULT '',
    `bBrkrError` TINYTEXT NOT NULL DEFAULT '',
    `sParentNumber` BIGINT NOT NULL DEFAULT 0,
    `sActiveSize` INT NOT NULL DEFAULT 0,
    `sBrkrStatus` TINYTEXT NOT NULL DEFAULT '',
    `sBrkrError` TINYTEXT NOT NULL DEFAULT '',
    `ddMult` DOUBLE NOT NULL DEFAULT 0,
    `underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'future underliers per contract',
    `underlierType` ENUM('None','Equity','Other','FX') NOT NULL DEFAULT 'None' COMMENT 'type of underlier (affects $greek calculations)',
    `pointValue` DOUBLE NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (hedge target)',
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX','DKK','GEL') NOT NULL DEFAULT 'None',
    `liveHedgeMark` DOUBLE NOT NULL DEFAULT 0 COMMENT 'hedgeSecKey live mark (mid-market) [zero in SRSE]',
    `updtReason` VARCHAR(20) NOT NULL DEFAULT '',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'SR system timestamp',
    PRIMARY KEY USING HASH (`hedgeTarget_tk`,`hedgeTarget_at`,`hedgeTarget_ts`,`accnt`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `hedgeTarget_at`,
    `hedgeTarget_ts`,
    `hedgeTarget_tk`,
    `accnt`,
    `clientFirm`,
    `hedgeSecKey_at`,
    `hedgeSecKey_ts`,
    `hedgeSecKey_tk`,
    `hedgeSecKey_yr`,
    `hedgeSecKey_mn`,
    `hedgeSecKey_dy`,
    `hedgeSecType`,
    `securityDesc`,
    `ssaleFlag`,
    `positionType`,
    `hedgeState`,
    `hedgeText`,
    `uPrc`,
    `isDeltaMixed`,
    `accumulatorError`,
    `grpDeltaBot`,
    `grpDeltaSld`,
    `grpDDeltaBot`,
    `grpDDeltaSld`,
    `lastGrpFillDttm`,
    `ctrlDeltaBot`,
    `ctrlDeltaSld`,
    `ctrlDDeltaBot`,
    `ctrlDDeltaSld`,
    `lastCtrlFillDttm`,
    `absOptCn`,
    `netDelta`,
    `netDDelta`,
    `openHedgeTime`,
    `hedgeTargetVol`,
    `expectedPrcRange`,
    `opnDeltaBot`,
    `opnDeltaSld`,
    `opnDDeltaBot`,
    `opnDDeltaSld`,
    `clsDeltaBot`,
    `clsDeltaSld`,
    `clsDDeltaBot`,
    `clsDDeltaSld`,
    `slippagePnL`,
    `slippageUnitPnL`,
    `slippageNormPnL`,
    `minDDeltaBand`,
    `maxDDeltaBand`,
    `bParentNumber`,
    `bActiveSize`,
    `bBrkrStatus`,
    `bBrkrError`,
    `sParentNumber`,
    `sActiveSize`,
    `sBrkrStatus`,
    `sBrkrError`,
    `ddMult`,
    `underliersPerCn`,
    `underlierType`,
    `pointValue`,
    `pointCurrency`,
    `liveHedgeMark`,
    `updtReason`,
    `timestamp`
FROM `SRTrade`.`MsgSRHedgePolicyState`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `hedgeTarget_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `hedgeTarget_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `hedgeTarget_tk` = 'Example_hedgeTarget_tk'
  AND
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SRHedgePolicyState' ORDER BY ordinal_position ASC;